<?php
/*
 * Title: myForum database class
*/

class mf_DB{

	function __construct(){

	}

	/**
	 * Return topic posts sql result
	 *
	 * @param $topicId
	 *   Topic id
	 * @return
	 *   MySql result
	 */
	function getTopicPosts($topicId, $offset, $postsPerPage){
	
		global $modx;
		$topicId = intval($topicId);
		return $modx->db->query("
			SELECT
				p.*, u.username
			FROM ".$modx->getFullTableName('myforum_posts')." p
			LEFT JOIN
				".$modx->getFullTableName('web_users')." u
			ON
				u.id = p.author_id
			WHERE
				p.topic_id = ".$topicId."
			ORDER BY
				created_at 
			LIMIT ".$offset.", ".$postsPerPage
			);
	
	}
	
	/**
	 * Return topics list sql result
	 *
	 * @return
	 *   MySql result
	 */
	function getTopicsList($offset, $postsPerPage){
	
		global $modx;
		
		return $modx->db->query("
			SELECT
				t.*, u.username as authorName, lu.username as lastPosterName
			FROM ".$modx->getFullTableName('myforum_topics')."t
			LEFT JOIN
				".$modx->getFullTableName('web_users')." u
			ON
				u.id = t.starter_user_id
			LEFT JOIN
				".$modx->getFullTableName('web_users')." lu
			ON
				lu.id = t.last_post_user_id
			ORDER BY
				last_post_at DESC
			LIMIT ".$offset.", ".$postsPerPage
			);
		
	}
	
	/**
	 * Remove topic
	 *
	 * @param $topicId
	 *   Topic id
	 * @return
	 *   MySql result
	 */
	function removeTopic($topicId){
	
		global $modx;
	
		if( !$topicId ) return false;
	
		$topicId = intval($topicId);
		
		$modx->db->delete($modx->getFullTableName( 'myforum_posts' ), 'topic_id = '.$topicId);
		$modx->db->delete($modx->getFullTableName( 'myforum_topics' ), 'id = '.$topicId);
	
	}
	
	/**
	 * Get post data
	 *
	 * @param $postId
	 *   Post id
	 * @return
	 *   MySql result
	 */
	function getPostData($postId){
	
		global $modx;
		return $modx->db->getRow($modx->db->select("*", $modx->getFullTableName('myforum_posts'), 'id='.intval($postId), '1'));
	
	}
	
	/**
	 * Insert new post and update topic data
	 *
	 * @param $fields
	 *   Array of vars
	 */
	function addNewPost($fields){
	
		global $modx;
		$postId = $modx->db->insert( $fields, $modx->getFullTableName( 'myforum_posts' ));

		$modx->db->query('
			UPDATE
				'.$modx->getFullTableName('myforum_topics').'
			SET
				posts_count = posts_count+1,
				last_post_at = '.time().',
				last_post_user_id = '.intval($fields['author_id']).'
			WHERE
				id = '.intval($fields['topic_id']).'
			LIMIT 1'
			);
	
	}
	
	/**
	 * Update post data
	 *
	 * @param $postId
	 *   Post id
	 * @param $postTitle
	 *   Post title
	 * @param $postContent
	 *   Post content
	 * @return
	 *   true || false
	 */
	function updatePostData($postId, $postTitle, $postContent){
	
		global $modx;
		
		if( !$postId || !$postTitle || !$postContent ){
			return "";
		}
		
		return $modx->db->update( array('title' => $postTitle, 'content' => $postContent), $modx->getFullTableName('myforum_posts'), 'id = '.$postId );
	
	}
	
	/**
	 * Remove post, recount topic stats
	 *
	 */
	function removePost($id){

		global $modx;
	
		$id = intval($id);
		
		$topicId = $modx->db->getValue($modx->db->select("topic_id", $modx->getFullTableName('myforum_posts'), 'id='.$id));
		$totalPosts = $modx->db->getValue($modx->db->select("count(*)", $modx->getFullTableName('myforum_posts'), 'topic_id='.$topicId));
		
		if( !$topicId || !$totalPosts ){ return ""; }
		
		$modx->db->delete($modx->getFullTableName( 'myforum_posts' ), 'id = '.$id);
		
		// if there is no more posts in topic, remove topic too
		if( $totalPosts == 1 ){
			$modx->db->delete($modx->getFullTableName( 'myforum_topics' ), 'id = '.$topicId);
			return 'front';
		} else {
			// remove post and update topic data
			$newLastRow = $modx->db->getRow($modx->db->select("created_at, author_id", $modx->getFullTableName('myforum_posts'), 'topic_id='.$topicId, 'created_at DESC', '1'));
			$modx->db->update( array('posts_count' => 'posts_count-1', 'last_post_at' => $newLastRow['created_at'], 'last_post_user_id' => $newLastRow['author_id']), $modx->getFullTableName('myforum_topics'), 'id = '.$topicId );
			return 'topic';
		}
	
	}
	
	
	/**
	 * Return topic posts count
	 *
	 * @param $topicId
	 *   Topic id
	 * @return
	 *   integer
	 */
	function getPostsCount($topicId){
		
		global $modx;
		return $modx->db->getValue($modx->db->select("count(*)", $modx->getFullTableName('myforum_posts'), 'topic_id='.intval($topicId)));
	
	}
	
	
	/**
	 * Return forum topics count
	 *
	 * @return
	 *   integer
	 */
	function getTopicsCount(){
		
		global $modx;
		return $modx->db->getValue($modx->db->select("count(*)", $modx->getFullTableName('myforum_topics')));
	
	}
	
	
	/**
	 * Update topic views
	 *
	 * @param $topicId
	 *   Topic id
	 * @return
	 *   true || false
	 */
	function incTopicViews($topicId){
		
		global $modx;
		return $modx->db->query('
			UPDATE
				'.$modx->getFullTableName('myforum_topics').'
			SET
				views_count = views_count+1
			WHERE
				id = '.intval($topicId).'
			LIMIT 1'
			);
	
	}
	
	/**
	 * Check for myForum DB table exists and create them if there are no tables (taken from Jot)
	 *
	 * @param $path
	 *   path for myForum snippet
	 *
	 */
	function firstRun($path) {
		global $modx;
		$myForumTopics = $GLOBALS['table_prefix'].'myforum_topics';

		$rs = $modx->db->query("SHOW TABLES LIKE '".$myForumTopics."'");
		$count = $modx->db->getRecordCount($rs);
		
		if ($count==0) {
			$fh = fopen($path."/myforum.install.db.sql", 'r');
			$idata = '';
			while (!feof($fh)) {
				$idata .= fread($fh, 1024);
			}
			fclose($fh);
			$idata = str_replace("\r", '', $idata);
			$idata = str_replace('{PREFIX}',$GLOBALS['table_prefix'], $idata);
			$sql_array = explode("\n\n", $idata);
			foreach($sql_array as $sql_entry) {
				$sql_do = trim($sql_entry, "\r\n; ");
				$modx->db->query($sql_do);	
			}
		}
	}
	
}

?>